"""
模拟浏览器行为，获取东方财富的股票

基本信息：

http://quote.eastmoney.com/sh600460.html

http://quote.eastmoney.com/sz002266.html

http://quote.eastmoney.com/sz300001.html

http://quote.eastmoney.com/kcb/688333.html
"""

# pip install selenium

from selenium import webdriver
import time, datetime
from bs4 import BeautifulSoup
import pymysql
import random
import pandas as pd
import re

# import os
# os.chdir('src/getData')

# 数据库连接
# 本机
conn1 = pymysql.connect(host='localhost', user='root', passwd='root', db='stock', charset="utf8")
# 阿里云试用
conn3 = pymysql.connect(host='rm-2vc83ot65e551h73beo.mysql.cn-chengdu.rds.aliyuncs.com', port=3306, user='river', passwd='lihua201314SJ', db='stock', charset="utf8")

conn = conn3
cursor = conn.cursor()

# 获取所有的代码
# 全部的股票历史数据
sql0 = """
SELECT DISTINCT `code`,name FROM stock.`stock_history_money_d` 
WHERE price>0 and day_no>=DATE_SUB(CURDATE(),INTERVAL 1 DAY)
and code regexp '^00|^60' 
and name not regexp 'ST'
"""

code_list = pd.read_sql(sql0, conn).to_dict('records')

print(f"共有{len(code_list)}只股票待抓取！")

day_no = str(datetime.date.today())


def change_unit(num):
    if num[-1] == '亿':
        return float(num[:-1]) * 100000000
    elif num[-1] == '万':
        return float(num[:-1]) * 10000
    elif num == '' or num == ' ' or num == '-':
        return 0
    else:
        return int(num[:-3])


browser = webdriver.Chrome('chromedriver2.exe')

for item in code_list:
    code = item['code']
    name = item['name']
    # 进入网站
    if re.search(r'^00', code, re.I):
        _url = "http://quote.eastmoney.com/sz{}.html".format(code)
    elif re.search(r'^60', code, re.I):
        _url = "http://quote.eastmoney.com/sh{}.html".format(code)
    browser.get(_url)
    time.sleep(1.5)
    try:
        # 使用beautifulSoup解析网页标签
        html = BeautifulSoup(browser.page_source, "lxml")
        # 获取估计市值等基础数据
        # 当前价格
        price_today = html.select("#price9")[0].text  # 当前价格
        up_num_today = html.select("#km1")[0].text  # 涨跌金额
        up_rate_today = html.select("#km2")[0].text  # 涨跌幅
        # 其他基础信息
        pe_today = html.select("#gt6")[0].text  # 市盈率
        market_value_flow_today = html.select("#gt14")[0].text  # 流通市值
        market_value_all_today = html.select("#gt7")[0].text  # 总市值
        # 获取公司核心数据
        # money_info_today = html.select("#rtp2 tbody tr")
        # basic_info = []
        # for jtem in money_info_today:
        #     jtem_list = jtem.find_all('td')
        #     jtem_list_clean = [str(i.text).strip().replace(' ', '').split(':') for i in jtem_list]
        #     code = code
        #     name = name
        #     basic_info = basic_info + jtem_list_clean
        # df_basic_info = pd.DataFrame(basic_info)
        """
                  0           1
        0     收益(二)       3.173
        1     PE(动)        8.55
        2     每股净资产      43.316
        3       市净率        1.25
        4       总营收       6356亿
        5        同比       0.90%
        6       净利润      580.0亿
        7        同比     -15.55%
        8       毛利率       0.00%
        9       净利率      10.69%
        10      ROE       7.50%
        11      负债率      89.59%
        12      总股本      182.8亿
        13       总值       9924亿
        14      流通股      108.3亿
        15       流值       5881亿
        16  每股未分配利润     30.285元
        17     上市时间  2007-03-01
        """
        sql_check = f"select * from stock.stock_basic_info_d where code='{code}' and day_no='{day_no}'"
        res = cursor.execute(sql_check)
        if res == 1:
            # 更新
            sql = f"""
            update stock.`stock_basic_info_d`
            set
            price                  = {price_today},
            pe                     = {pe_today},
            market_value_flow      = {change_unit(market_value_flow_today)},
            market_value_str_flow  = '{market_value_flow_today}',
            market_value_all       = {change_unit(market_value_all_today)},
            market_value_str_all   = '{market_value_all_today}',
            load_time=current_timestamp
            where code='{code}' and day_no='{day_no}'
            """
        else:
            # 插入
            sql = f"""
            insert into stock.stock_basic_info_d
            (code,name,day_no,price,pe,market_value_flow,market_value_str_flow,market_value_all,market_value_str_all,load_time)
            values('{code}','{name}','{day_no}',{price_today},{pe_today},{change_unit(market_value_flow_today)},'{market_value_flow_today}',{change_unit(
                market_value_all_today)},'{market_value_all_today}',current_timestamp)
            """
        cursor.execute(sql)
        conn.commit()
        print(code, '\t', name)
        time.sleep(0.5)
    except Exception as e:
        print(e)
        print(sql)

cursor.close()
conn.close()
